<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  
  <title>第23篇 MySQL索引 | Hexo</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  <meta name="description" content="第23篇：MySQL索引管理索引分类 分为聚集索引和⾮聚集索引。聚集索引 每个表有且⼀定会有⼀个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采⽤B+树结构保存在⽂件中，叶⼦节点存储主键的值以及对应记录的数据，⾮叶⼦节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会⾃动给每条记录添加⼀个隐藏的rowid字段（默认4个字节）作为主键，⽤rowid构建聚集索引。 聚集">
<meta property="og:type" content="article">
<meta property="og:title" content="第23篇 MySQL索引">
<meta property="og:url" content="https://goofyer.gitee.io/notes-on-computer-expertise/2022/01/16/%E6%95%B0%E6%8D%AE%E5%BA%93/%E7%AC%AC23%E7%AF%87%20MySQL%E7%B4%A2%E5%BC%95/index.html">
<meta property="og:site_name" content="Hexo">
<meta property="og:description" content="第23篇：MySQL索引管理索引分类 分为聚集索引和⾮聚集索引。聚集索引 每个表有且⼀定会有⼀个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采⽤B+树结构保存在⽂件中，叶⼦节点存储主键的值以及对应记录的数据，⾮叶⼦节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会⾃动给每条记录添加⼀个隐藏的rowid字段（默认4个字节）作为主键，⽤rowid构建聚集索引。 聚集">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2022-01-16T08:36:12.000Z">
<meta property="article:modified_time" content="2022-02-10T12:50:12.925Z">
<meta property="article:author" content="John Doe">
<meta name="twitter:card" content="summary">
  
    <link rel="alternate" href="/notes-on-computer-expertise/atom.xml" title="Hexo" type="application/atom+xml">
  
  
    <link rel="shortcut icon" href="/notes-on-computer-expertise/favicon.png">
  
  
    
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/typeface-source-code-pro@0.0.71/index.min.css">

  
  
<link rel="stylesheet" href="/notes-on-computer-expertise/css/style.css">

  
    
<link rel="stylesheet" href="/notes-on-computer-expertise/fancybox/jquery.fancybox.min.css">

  
<meta name="generator" content="Hexo 5.4.0"></head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/notes-on-computer-expertise/" id="logo">Hexo</a>
      </h1>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/notes-on-computer-expertise/">Home</a>
        
          <a class="main-nav-link" href="/notes-on-computer-expertise/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/notes-on-computer-expertise/atom.xml" title="RSS Feed"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Search"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="https://goofyer.gitee.io/notes-on-computer-expertise"></form>
      </div>
    </div>
  </div>
</header>

      <div class="outer">
        <section id="main"><article id="post-数据库/第23篇 MySQL索引" class="h-entry article article-type-post" itemprop="blogPost" itemscope itemtype="https://schema.org/BlogPosting">
  <div class="article-meta">
    <a href="/notes-on-computer-expertise/2022/01/16/%E6%95%B0%E6%8D%AE%E5%BA%93/%E7%AC%AC23%E7%AF%87%20MySQL%E7%B4%A2%E5%BC%95/" class="article-date">
  <time class="dt-published" datetime="2022-01-16T08:36:12.000Z" itemprop="datePublished">2022-01-16</time>
</a>
    
  <div class="article-category">
    <a class="article-category-link" href="/notes-on-computer-expertise/categories/Mysql/">Mysql</a>
  </div>

  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="p-name article-title" itemprop="headline name">
      第23篇 MySQL索引
    </h1>
  

      </header>
    
    <div class="e-content article-entry" itemprop="articleBody">
      
        <h1 id="第23篇：MySQL索引管理"><a href="#第23篇：MySQL索引管理" class="headerlink" title="第23篇：MySQL索引管理"></a>第23篇：MySQL索引管理</h1><h2 id="索引分类"><a href="#索引分类" class="headerlink" title="索引分类"></a><strong>索引分类</strong></h2><ul>
<li>分为<strong>聚集索引</strong>和<strong>⾮聚集索引</strong>。<h2 id="聚集索引"><a href="#聚集索引" class="headerlink" title="聚集索引"></a><strong>聚集索引</strong></h2></li>
<li>每个表有且⼀定会有⼀个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采⽤B+树结构保存在⽂件中，叶⼦节点存储主键的值以及对应记录的数据，⾮叶⼦节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会⾃动给每条记录添加⼀个隐藏的rowid字段（默认4个字节）作为主键，⽤rowid构建聚集索引。</li>
<li>聚集索引在mysql中又叫<strong>主键索引</strong>。</li>
<li><strong>⾮聚集索引（辅助索引）</strong><ul>
<li>mysql中⾮聚集索引分为<ul>
<li>单列索引<ul>
<li>即⼀个索引只包含⼀个列。</li>
</ul>
</li>
<li>多列索引（⼜称复合索引）<ul>
<li>即⼀个索引包含多个列。</li>
</ul>
</li>
<li>唯⼀索引<ul>
<li>索引列的值必须唯⼀，允许有⼀个空值。<h2 id="索引管理"><a href="#索引管理" class="headerlink" title="索引管理"></a>索引管理</h2></li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>创建索引<ul>
<li>⽅式1：<ul>
<li>create [unique] index 索引名称 on 表名(列名[(length)]);</li>
</ul>
</li>
<li>⽅式2：<ul>
<li>alter 表名 add [unique] index 索引名称 on (列名[(length)]);<ul>
<li>如果字段是char、varchar类型，length可以⼩于字段实际长度，如果是blog、</li>
<li>text等长⽂本类型，必须指定length。</li>
<li>[unique]：中括号代表可以省略，如果加上了unique，表⽰创建唯⼀索引。</li>
<li>如果table后⾯只写⼀个字段，就是单列索引，如果写多个字段，就是复合索引，多个字段之间⽤逗号隔开。</li>
</ul>
</li>
</ul>
</li>
</ul>
</li>
<li>删除索引<ul>
<li>drop index 索引名称 on 表名;</li>
</ul>
</li>
<li>查看索引<ul>
<li>查看某个表中所有的索引信息如下：</li>
<li>show index from 表名;</li>
</ul>
</li>
<li>索引修改<ul>
<li>可以先删除索引，再重建索引。<h3 id="示例"><a href="#示例" class="headerlink" title="示例"></a>示例</h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br></pre></td><td class="code"><pre><span class="line">准备<span class="number">200</span>万数据</span><br><span class="line">建库</span><br><span class="line"><span class="keyword">DROP</span> DATABASE IF <span class="keyword">EXISTS</span> javacode2018; <span class="keyword">CREATE</span> DATABASE javacode2018;</span><br><span class="line"></span><br><span class="line">USE javacode2018;</span><br><span class="line"></span><br><span class="line">建表</span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">TABLE</span> IF <span class="keyword">EXISTS</span> test1; <span class="keyword">CREATE</span> <span class="keyword">TABLE</span> test1 (</span><br><span class="line">id	<span class="type">INT</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;编号&#x27;</span>,</span><br><span class="line">name	<span class="type">VARCHAR</span>(<span class="number">20</span>) <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">sex TINYINT <span class="keyword">NOT</span> <span class="keyword">NULL</span> COMMENT <span class="string">&#x27;性别,1：男，2：⼥&#x27;</span>,</span><br><span class="line">email	<span class="type">VARCHAR</span>(<span class="number">50</span>)</span><br><span class="line">);</span><br><span class="line"></span><br><span class="line">准备数据</span><br><span class="line"><span class="keyword">DROP</span> <span class="keyword">PROCEDURE</span> IF <span class="keyword">EXISTS</span> proc1; DELIMITER $</span><br><span class="line"><span class="keyword">CREATE</span> <span class="keyword">PROCEDURE</span> proc1() <span class="keyword">BEGIN</span></span><br><span class="line"><span class="keyword">DECLARE</span> i <span class="type">INT</span> <span class="keyword">DEFAULT</span> <span class="number">1</span>; <span class="keyword">START</span> TRANSACTION;</span><br><span class="line">WHILE i <span class="operator">&lt;=</span> <span class="number">2000000</span> DO</span><br><span class="line"><span class="keyword">INSERT</span> <span class="keyword">INTO</span> test1 (id, name, sex, email) <span class="keyword">VALUES</span> (i,concat(<span class="string">&#x27;javacode&#x27;</span>,i),if(<span class="built_in">mod</span>(i,<span class="number">2</span>),<span class="number">1</span>,<span class="number">2</span>),concat(<span class="string">&#x27;javacode&#x27;</span>,i,<span class="string">&#x27;@163.com &#x27;</span>));</span><br><span class="line"><span class="keyword">SET</span> i <span class="operator">=</span> i <span class="operator">+</span> <span class="number">1</span>;</span><br><span class="line">if i<span class="operator">%</span><span class="number">10000</span><span class="operator">=</span><span class="number">0</span> <span class="keyword">THEN</span> <span class="keyword">COMMIT</span>;</span><br><span class="line"><span class="keyword">START</span> TRANSACTION; <span class="keyword">END</span> IF;</span><br><span class="line"><span class="keyword">END</span> WHILE;</span><br><span class="line"><span class="keyword">COMMIT</span>;</span><br><span class="line"><span class="keyword">END</span> $</span><br><span class="line"></span><br><span class="line">DELIMITER ;</span><br><span class="line"><span class="keyword">CALL</span> proc1();</span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">FROM</span> test1;</span><br><span class="line"><span class="string">&#x27;&#x27;&#x27;</span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string"></span></span><br><span class="line"><span class="string"></span></span><br></pre></td></tr></table></figure></li>
</ul>
</li>
</ul>

      
    </div>
    <footer class="article-footer">
      <a data-url="https://goofyer.gitee.io/notes-on-computer-expertise/2022/01/16/%E6%95%B0%E6%8D%AE%E5%BA%93/%E7%AC%AC23%E7%AF%87%20MySQL%E7%B4%A2%E5%BC%95/" data-id="cl403sxs30036f8vuc91k4xj1" data-title="第23篇 MySQL索引" class="article-share-link">Share</a>
      
      
      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/notes-on-computer-expertise/2022/01/16/%E5%B0%8F%E9%A1%B9%E7%9B%AE%E6%A8%A1%E6%9D%BF/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Newer</strong>
      <div class="article-nav-title">
        
          小项目模板
        
      </div>
    </a>
  
  
    <a href="/notes-on-computer-expertise/2022/01/16/%E7%BC%96%E5%86%99%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/%E5%A6%82%E4%BD%95%E7%94%A8C-%E7%BC%96%E5%86%99%E4%B8%80%E4%B8%AA%E4%BA%8C%E8%BF%9B%E5%88%B6%E7%BC%96%E8%BE%91%E5%99%A8/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Older</strong>
      <div class="article-nav-title">如何用C#编写一个二进制编辑器</div>
    </a>
  
</nav>

  
</article>


</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Categories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/C-%E5%B7%A5%E5%85%B7/">C#工具</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/C-%E5%B7%A5%E5%85%B7/">C++工具</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/Mysql/">Mysql</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/blender/">blender</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/linux/">linux</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/manim/">manim</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/spring/">spring</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/vba%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C/">vba基础操作</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E5%88%9B%E9%80%A0%E6%A8%A1%E5%BC%8F/">创造模式</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F/">操作系统</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">操作系统基础知识</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%93%8D%E4%BD%9C%E7%B3%BB%E7%BB%9F%E5%BC%80%E5%8F%91/">操作系统开发</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E5%AD%A6/">数学</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84/">数据结构</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%95%B0%E6%8D%AE%E7%BB%93%E6%9E%84%E5%9F%BA%E7%A1%80%E7%9F%A5%E8%AF%86/">数据结构基础知识</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%A8%A1%E6%9D%BF/">模板</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E6%B7%B1%E5%BA%A6%E5%AD%A6%E4%B9%A0/">深度学习</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%8B%B1%E8%AF%AD/">英语</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%99%9A%E5%B9%BB4/">虚幻4</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BB%84%E6%88%90%E5%8E%9F%E7%90%86/">计算机组成原理</a></li><li class="category-list-item"><a class="category-list-link" href="/notes-on-computer-expertise/categories/%E8%AE%A1%E7%AE%97%E6%9C%BA%E7%BD%91%E7%BB%9C/">计算机网络</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tags</h3>
    <div class="widget">
      <ul class="tag-list" itemprop="keywords"><li class="tag-list-item"><a class="tag-list-link" href="/notes-on-computer-expertise/tags/C/" rel="tag">C++</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Tag Cloud</h3>
    <div class="widget tagcloud">
      <a href="/notes-on-computer-expertise/tags/C/" style="font-size: 10px;">C++</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/05/">May 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/03/">March 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/02/">February 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2022/01/">January 2022</a></li><li class="archive-list-item"><a class="archive-list-link" href="/notes-on-computer-expertise/archives/2021/12/">December 2021</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Recent Posts</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/31/vue/%E5%89%8D%E7%AB%AF%E6%A1%86%E6%9E%B6%E9%AA%8C%E8%AF%81%E7%A0%81%E6%97%A0%E6%B3%95%E6%98%BE%E7%A4%BA/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/26/%E6%95%B0%E6%8D%AE%E5%BA%93/navicat%E5%AE%89%E8%A3%85/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/25/%E8%99%9A%E5%B9%BB4%E5%BC%95%E6%93%8E%E5%BC%80%E5%8F%91/%E8%99%9A%E5%B9%BB%E5%9B%9B%20mod%E5%88%B6%E4%BD%9C/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/23/python/python%E6%89%B9%E9%87%8F%E7%94%9F%E6%88%90%E6%95%B0%E6%8D%AE/">(no title)</a>
          </li>
        
          <li>
            <a href="/notes-on-computer-expertise/2022/05/23/vba%E5%9F%BA%E7%A1%80%E6%93%8D%E4%BD%9C%E7%AC%94%E8%AE%B0/EXCEL%E5%9F%BA%E7%A1%80%E6%95%99%E7%A8%8B/">(no title)</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      
      &copy; 2022 John Doe<br>
      Powered by <a href="https://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>

    </div>
    <nav id="mobile-nav">
  
    <a href="/notes-on-computer-expertise/" class="mobile-nav-link">Home</a>
  
    <a href="/notes-on-computer-expertise/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    


<script src="/notes-on-computer-expertise/js/jquery-3.4.1.min.js"></script>



  
<script src="/notes-on-computer-expertise/fancybox/jquery.fancybox.min.js"></script>




<script src="/notes-on-computer-expertise/js/script.js"></script>





  </div>
<script type="text/x-mathjax-config">
    MathJax.Hub.Config({
        tex2jax: {
            inlineMath: [ ["$","$"], ["\\(","\\)"] ],
            skipTags: ['script', 'noscript', 'style', 'textarea', 'pre', 'code'],
            processEscapes: true
        }
    });
    MathJax.Hub.Queue(function() {
        var all = MathJax.Hub.getAllJax();
        for (var i = 0; i < all.length; ++i)
            all[i].SourceElement().parentNode.className += ' has-jax';
    });
</script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.1/MathJax.js?config=TeX-MML-AM_CHTML"></script>
</body>
</html>